package com.situ.company.employee.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.company.employee.model.EmployeeModel;
import com.situ.company.util.FmtEmpty;
import com.situ.company.util.JDBCUtil;

public class EmployeeDao {
	private static String table = "new_company.employee";
	private static String fields = "code,pass,name,code_dept,image";
	private Connection conn;
	private PreparedStatement ps;

	/**
	 * 添加
	 * 
	 * @param model
	 * @return
	 */
	public Integer insert(EmployeeModel model) {
		StringBuffer sql = new StringBuffer("insert into ");
		sql.append(table).append('(').append(fields).append(")values(?,?,?,?,?)");
		try {
			conn = JDBCUtil.getConnertion();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, model.getCode());
			ps.setString(2, model.getPass());
			ps.setString(3, model.getName());
			ps.setString(4, model.getCodeDept());
			ps.setString(5, model.getImage());
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}

	/**
	 * 删除
	 * 
	 * @param model
	 * @return
	 */
	public Integer delete(EmployeeModel model) {
		StringBuffer sql = new StringBuffer("delete from ");
		sql.append(table);// where code = ?
		List<Object> list = appendWhere(sql, model);
		try {
			conn = JDBCUtil.getConnertion();
			ps = conn.prepareStatement(sql.toString());
			for (int i = 0; i < list.size(); i++)
				ps.setObject(i + 1, list.get(i));
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}

	/**
	 * 拼接语句
	 * 
	 * @param sql
	 * @param model
	 * @return
	 */
	private List<Object> appendWhere(StringBuffer sql, EmployeeModel model) {
		List<Object> list = new ArrayList<>();
		sql.append(" where 1=1 ");
		String code = model.getCode();
		if (!FmtEmpty.isEmpty(code)) {
			sql.append(" and code like ? ");
			list.add(code);
		}
		String name = model.getName();
		if (!FmtEmpty.isEmpty(name)) {
			sql.append(" and name like ? ");
			list.add(name);
		}
		String pass = model.getPass();
		if (!FmtEmpty.isEmpty(pass)) {
			sql.append(" and pass like ? ");
			list.add(pass);
		}
		String codeDept = model.getCodeDept();
		if (!FmtEmpty.isEmpty(codeDept)) {
			sql.append(" and code_dept like ? ");
			list.add(codeDept);
		}
		String order = model.getOrderby();
		if (order != null && !order.trim().isEmpty()) {
			sql.append(" order by ").append(order);
		}
		if (model.isPageOn()) {
			sql.append(" limit ?,? ");
			list.add(model.getRowStart());
			list.add(model.getRowCount());
		}
		return list;
	}

	/**
	 * 修改
	 * 
	 * @param model
	 * @return
	 */
	public Integer update(EmployeeModel model) {
		StringBuffer sql = new StringBuffer("update ");
		sql.append(table);
		// sql.append(" set pass=?,name=?,code_dept=?,image=? where code=?" );
		List<Object> list = appendSet(sql, model);
		try {
			conn = JDBCUtil.getConnertion();
			ps = conn.prepareStatement(sql.toString());
			for (int i = 0; i < list.size(); i++)
				ps.setObject(i + 1, list.get(i));
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}

	/**
	 * 修改拼接
	 * 
	 * @param sql
	 * @param model
	 * @return
	 */
	private List<Object> appendSet(StringBuffer sql, EmployeeModel model) {
		List<Object> list = new ArrayList<>();
		sql.append(" set id=id ");
		String pass = model.getPass();
		if (pass != null) {// !"".equals(code.trim())
			sql.append(" ,pass = ? ");
			list.add(pass);
		}
		String name = model.getName();
		if (name != null) {// !"".equals(code.trim())
			sql.append(" ,name = ? ");
			list.add(name);
		}
		String codeDept = model.getCodeDept();
		if (codeDept != null) {// !"".equals(code.trim())
			sql.append(" ,code_dept = ? ");
			list.add(codeDept);
		}
		String image = model.getImage();
		if (image != null) {// !"".equals(code.trim())
			sql.append(" ,image = ? ");
			list.add(image);
		}
		sql.append(" where code = ?");
		list.add(model.getCode());
		return list;
	}

	/**
	 * 查询
	 * 
	 * @param model
	 * @return
	 */
	public List<EmployeeModel> selectList(EmployeeModel model) {
		StringBuffer sql = new StringBuffer("select ");
		sql.append(fields);
		sql.append(",(select d.name from department d where d.code=employee.code_dept)name_dept");
		sql.append(" from ").append(table);
		List<Object> list = appendWhere(sql, model);
		List<EmployeeModel> result = new ArrayList<>();
		ResultSet rs = null;
		try {
			conn = JDBCUtil.getConnertion();
			ps = conn.prepareStatement(sql.toString());
			for (int i = 0; i < list.size(); i++)
				ps.setObject(i + 1, list.get(i));
			rs = ps.executeQuery();
			while (rs.next()) {
				EmployeeModel m = new EmployeeModel();
				m.setCode(rs.getString("code"));
				m.setPass(rs.getString("pass"));
				m.setName(rs.getString("name"));
				m.setCodeDept(rs.getString("code_dept"));
				m.setImage(rs.getString("image"));
				m.setNameDept(rs.getString("name_dept"));
				result.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps, rs);
		}
		return result;
	}

	/**
	 * 记录条数
	 * 
	 * @param model
	 * @return
	 */
	public int selectCount(EmployeeModel model) {
		StringBuffer sql = new StringBuffer("select count(id) from ");
		sql.append(table);
		List<Object> vals = appendWhere(sql, model);
		ResultSet rs = null;
		try {
			conn = JDBCUtil.getConnertion();
			ps = conn.prepareStatement(sql.toString());
			for (int i = 0; i < vals.size(); i++)
				ps.setObject(i + 1, vals.get(i));
			rs = ps.executeQuery();
			if (rs.next())
				return rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps, rs);
		}
		return 0;
	}

}
